/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entities.Category;
import factory.DBConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author theworldiwant
 */
public class MySQLCategoryDAO extends CategoriesDAO{

    private int getCurId(){
            int ctr=0;
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt2;
        try {
            pstmt2 = conn.prepareStatement("select  idcategory from category");
            ResultSet rs=pstmt2.executeQuery();
            while(rs.next())
                ctr=rs.getInt("idcategory");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
           
            
        return ctr;
    }
    @Override
    public void addCatProd(int prodid, int catid) {
         try {
             //INSERT INTO `webdeve`.`category` (`idcategory`, `categoryId`, `productId`) VALUES (1, 1, 1);

            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO `webdeve`.`category` (`idcategory`, `categoryId`, `productId`) VALUES (?, ?, ?);");
            pstmt.setInt(1, getCurId()+1);
            pstmt.setInt(2, catid);
            pstmt.setInt(3, prodid);
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void deleteCatProd(int prodid, int catid) {
        try {
             // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("DELETE FROM `webdeve`.`category` WHERE `categoryId`='"+catid+"' and productId='"+prodid+"';");
            
            
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Category> getCatProd(int prodid) {
        //SELECT *from category where productid=prodid;
        LinkedList<Category> cs= new LinkedList<Category>();
        try {
             // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT *from category where productid='"+prodid+"';");
            ResultSet rs =pstmt.executeQuery();
            Category c;
            while(rs.next()){
                c= new Category();
                c.setIdCategory(rs.getInt("idcategory"));
                c.setCategoryId(rs.getInt("categoryId"));
                c.setProductId(rs.getInt("productId"));
                cs.add(c);
            }
            conn.close();
            return cs;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return null;
    }
    
}
